﻿using JavaDevToys.Rcl.Models;
using JavaDevToys.Rcl.Utils;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JavaDevToys.Rcl.Service.Impl
{
    public class JavaEnumParser : IJavaEnumParser
    {
        public List<DictCodeSystem> QueryCodeSystem(JavaProjectInfo javaProjectInfo, string keyWord)
        {
            List<DictCodeSystem> result = new List<DictCodeSystem>();
            string queryColumns = @"SELECT TOP 100 CODE_SYSTEM_ID, CODE_SYSTEM_NO, CODE_SYSTEM_NAME, CODE_SYSTEM_2ND_NAME, CODE_SYSTEM_EXT_NAME, DESCRIPTION, SPELL_NO, WB_NO, VERSION, BEGIN_DATE, END_DATE, ENABLED_FLAG, PUBLISH_DATE, SOURCE_ID, SOURCE_NAME, CREATED_ORG_LOCATION_ID, CREATED_USER_ID, CREATED_AT, MODIFIED_ORG_LOCATION_ID, MODIFIED_USER_ID, MODIFIED_AT FROM DICT_CODE_SYSTEM where 1=1 ";
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                queryColumns += "and (CODE_SYSTEM_NO like @KeyWord or CODE_SYSTEM_NAME like @KeyWord or SPELL_NO like @KeyWord or WB_NO like @KeyWord)";
            }
            string connectionString = SqlServerHelp.BuildConnectionString(javaProjectInfo.sqlserverInfo.Server, javaProjectInfo.sqlserverInfo.Database, javaProjectInfo.sqlserverInfo.UserName, javaProjectInfo.sqlserverInfo.Pwd);
            // 收集字段信息
            var columns = new List<ColumnInfo>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmdColumns = new SqlCommand(queryColumns, connection);
                cmdColumns.Parameters.AddWithValue("@KeyWord", $"%{keyWord}%");
                SqlDataReader readerColumns = cmdColumns.ExecuteReader();

                while (readerColumns.Read())
                {
                    result.Add(new DictCodeSystem()
                    {
                        codeSystemId = Convert.ToInt64(readerColumns["CODE_SYSTEM_ID"]),
                        codeSystemNo = readerColumns["CODE_SYSTEM_NO"].ToString(),
                        codeSystemName = readerColumns["CODE_SYSTEM_NAME"].ToString(),
                        codeSystem2ndName = readerColumns["CODE_SYSTEM_2ND_NAME"].ToString(),
                        codeSystemExtName = readerColumns["CODE_SYSTEM_EXT_NAME"].ToString(),
                        description = readerColumns["DESCRIPTION"].ToString(),
                        spellNo = readerColumns["SPELL_NO"].ToString(),
                        wbNo = readerColumns["WB_NO"].ToString(),
                        version = readerColumns["VERSION"] != DBNull.Value ? int.Parse(readerColumns["VERSION"].ToString()) : null,
                        beginDate = readerColumns["BEGIN_DATE"] != DBNull.Value ? (DateTime)readerColumns["BEGIN_DATE"] : null,
                        endDate = readerColumns["END_DATE"] != DBNull.Value ? (DateTime)readerColumns["END_DATE"] : null,
                        enabledFlag = readerColumns["ENABLED_FLAG"] != DBNull.Value ? short.Parse(readerColumns["ENABLED_FLAG"].ToString()) : null,
                        publishDate = readerColumns["PUBLISH_DATE"] != DBNull.Value ? (DateTime)readerColumns["PUBLISH_DATE"] : null,
                        sourceId = readerColumns["SOURCE_ID"].ToString(),
                        sourceName = readerColumns["SOURCE_NAME"].ToString(),
                        createdOrgLocationId = readerColumns["CREATED_ORG_LOCATION_ID"] != DBNull.Value ? Convert.ToInt64(readerColumns["CREATED_ORG_LOCATION_ID"]) : null,
                        createdUserId = readerColumns["CREATED_USER_ID"] != DBNull.Value ? Convert.ToInt64(readerColumns["CREATED_USER_ID"]) : null,
                        createdAt = readerColumns["CREATED_AT"] != DBNull.Value ? (DateTime)readerColumns["CREATED_AT"] : null,
                        modifiedOrgLocationId = readerColumns["MODIFIED_ORG_LOCATION_ID"] != DBNull.Value ? Convert.ToInt64(readerColumns["MODIFIED_ORG_LOCATION_ID"]) : null,
                        modifiedUserId = readerColumns["MODIFIED_USER_ID"] != DBNull.Value ? Convert.ToInt64(readerColumns["MODIFIED_USER_ID"]) : null,
                        modifiedAt = readerColumns["MODIFIED_AT"] != DBNull.Value ? (DateTime)readerColumns["MODIFIED_AT"] : null,
                    });
                }
                readerColumns.Close();
            }
            return result;
        }

        public List<DictDataSets> QueryDataSetsByCodeSystemIds(JavaProjectInfo javaProjectInfo, IEnumerable<long> ids)
        {
            if(ids.Count()==0)
                return new List<DictDataSets> ();
            List<DictDataSets> result = new List<DictDataSets>();
            string queryColumns = $@"SELECT DATA_VALUE_ID, CODE_SYSTEM_ID, DATA_VALUE_NO, DATA_VALUE_CN_NAME, DATA_VALUE_2ND_NAME, DATA_VALUE_EXT_NAME, DATA_VALUE_DESCRIPTION, SPELL_NO, WB_NO, CONCEPT_ID, CONCEPT_DESCRIPTION, ENABLED_FLAG, SORT, CREATED_ORG_LOCATION_ID, CREATED_USER_ID, CREATED_AT, MODIFIED_ORG_LOCATION_ID, MODIFIED_USER_ID, MODIFIED_AT FROM DICT_DATA_SETS where CODE_SYSTEM_ID in ({string.Join(",", ids.Select((id, index) => $"@id{index}"))}) ";
            string connectionString = SqlServerHelp.BuildConnectionString(javaProjectInfo.sqlserverInfo.Server, javaProjectInfo.sqlserverInfo.Database, javaProjectInfo.sqlserverInfo.UserName, javaProjectInfo.sqlserverInfo.Pwd);
            // 收集字段信息
            var columns = new List<ColumnInfo>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmdColumns = new SqlCommand(queryColumns, connection);
                var idsList = ids.ToList();
                for (int i = 0; i < idsList.Count(); i++)
                {
                    cmdColumns.Parameters.AddWithValue($"@id{i}", idsList[i]);
                }
                //cmdColumns.Parameters.AddWithValue("@KeyWord", $"({string.Join(",", ids)})");
                SqlDataReader readerColumns = cmdColumns.ExecuteReader();

                while (readerColumns.Read())
                {
                    result.Add(new DictDataSets()
                    {
                        dataValueId = Convert.ToInt64(readerColumns["DATA_VALUE_ID"]),
                        codeSystemId = Convert.ToInt64(readerColumns["CODE_SYSTEM_ID"]),
                        dataValueNo = readerColumns["DATA_VALUE_NO"].ToString(),
                        dataValueCnName = readerColumns["DATA_VALUE_CN_NAME"].ToString(),
                        dataValue2ndName = readerColumns["DATA_VALUE_2ND_NAME"].ToString(),
                        dataValueExtName = readerColumns["DATA_VALUE_EXT_NAME"].ToString(),
                        dataValueDescription = readerColumns["DATA_VALUE_DESCRIPTION"].ToString(),
                        spellNo = readerColumns["SPELL_NO"].ToString(),
                        wbNo = readerColumns["WB_NO"].ToString(),
                        conceptId = readerColumns["CONCEPT_ID"] != DBNull.Value ? Convert.ToInt64(readerColumns["CONCEPT_ID"]) : null,
                        conceptDescription = readerColumns["CONCEPT_DESCRIPTION"].ToString(),
                        enabledFlag = readerColumns["ENABLED_FLAG"] != DBNull.Value ? Convert.ToInt16(readerColumns["ENABLED_FLAG"]) : null,
                        sort = readerColumns["SORT"] != DBNull.Value ? Convert.ToInt32(readerColumns["SORT"]) : null,
                    });
                }
                readerColumns.Close();
            }
            return result;
        }
    }
}
